# -*- coding: utf-8 -*-
# @Author: hunan
# @Date: 2024/12/5 11:39
# @Description:
import json
import logging
import os
import random
import re
import threading
import uuid
from concurrent.futures import ThreadPoolExecutor, as_completed
from datetime import datetime, timedelta

from product_upload.core.basic import get_amazon_us_config_price_strategy, get_prod_price_by_class_name
from product_upload.domain.amazon_us.amazon_us_record import AmazonUsRecord
from product_upload.domain.amazon_us.amazon_us_tag import AmazonUsTag
from product_upload.domain.amazon_us.amazon_us_upload import AmazonUsUpload
from product_upload.domain.basic.basic_product import BasicProduct
from product_upload.domain.basic.basic_product_map import BasicProductMap
from product_upload.test.hand_sql_util import search_words_sys_prompt
from product_upload.util.basic.common_util import sleep_random_duration, extract_field_number, send_dingtalk_message, split_html_tag, split_langer_txt, remove_sensitive_words, shuffle_list_except_first_two_elements
from product_upload.util.basic.file_util import create_folder, copy_and_rename_file_with_row_check, write_to_amazon_template_xlsm, write_2d_list_to_txt, move_files_to_shared_folder
from product_upload.util.basic.mysql_util import db_batch_update, db_get_one, db_batch_insert, db_list_by_page, db_delete, db_list_by_page_v2,db_list_by_page_v3
from product_upload.util.basic.openai_util import model_request
from product_upload.util.seller.amazon_us_util import old_get_common_data, old_get_real_product_type, old_get_xlsm_data_for_product, old_get_template_file_path, old_load_amazon_us_data_to_cache

logger = logging.getLogger(__name__)


def gpt_second_select_tag(second_ai_text):
    sys_prompt = """
    Could you please help me select the option from the options that is closest to 'value'? Choose 'name' as the field name and 'value' as its value.
The example I provided:

name:top_material_type,value:Fabric,options:['Faux Fur', 'Faux Leather', 'Fur', 'Leather', 'Cotton', 'Suede', 'Cotton Blend', 'Linen', 'Wool', 'Acrylic']
name:material_type,value:Particle Board,options:[ 'Metal', 'Cedar', 'Silver', 'Wood', 'Sateen', 'Flannel', 'Steel',  'Fleece', 'Other', 'Rubberwood', 'Engineered Wood']

Strictly return using JSON format:
{"material_type":"Engineered Wood","top_material_type":"Cotton"}

Special emphasis is placed on:
No need to explain, just choose the option with the closest meaning in the options. Even if there are no similar options, choose the option that you think is close, using the original text of the options
    """
    second_ai_dict = model_request(True, second_ai_text, sys_prompt, "gpt-4o-mini")
    return second_ai_dict


def gpt_guess_field_value_task(product_information, field_definitions, field_options, result_dict):
    guess_sys_prompt = """
Fill in the product fields according to the provided product information and requirements, comply with any valid value constraints, and return the results in JSON format.
If there are clear values in the product information to fill in the product fields, use them directly.
If the field has valid value constraints, use the option closest to the meaning.
Steps
1. Review the provided product information and the list of fields to be filled in.
2. Identify the relevant values from the product information for each field.
3. If a field has validity constraints, choose the value with the closest meaning.
4. Ensure each field from the list is addressed, and populate with the corresponding value.
5.If the fields are related, make sure that the values they fill in are also similar, for example, fields: ["color", "color_map"]
6.If the meaning of the field is numerical, please fill in pure numbers directly without units
Strictly return using JSON format:
{"field1":"value1","field2":"value2",...}
"""
    user_prompt = f'need fill field:\n{json.dumps(list(field_definitions.keys()))}\n'
    if len(field_options) > 0:
        user_prompt += f'validity value constraints:\n{json.dumps(field_options)}\n'
    user_prompt += f'\nproduct information:\n{product_information}'
    guess_field_dict = model_request(True, user_prompt, guess_sys_prompt, "gpt-4o-mini")
    need_second_ai = []
    for k, v in guess_field_dict.items():
        options = field_options.get(k, [])
        if len(options) > 0 and v not in options and k != "color_name":
            # color_name 无需必选valid values中
            need_second_ai.append(f'name:{k},value:{v},options:{options}')
    if len(need_second_ai) > 0:
        need_second_ai_dict = gpt_second_select_tag('\n'.join(need_second_ai))
        guess_field_dict.update(need_second_ai_dict)
    result_dict.update(guess_field_dict)


def gpt_title_bullet_desc(product_info, result_dict):
    user_prompt = f'product information:\n{product_info}'
    prompt_list = old_get_common_data().get("prompt")
    sys_prompt = random.choice(prompt_list)
    desc_dict = model_request(True, user_prompt, sys_prompt, model="gpt-4o-mini", try_count=1)
    # 可能需要重新调用一次,如果没有核心字段回复
    if "product_title" not in desc_dict or "bullet_points" not in desc_dict or "product_description" not in desc_dict:
        # desc_dict = model_request(True, user_prompt, sys_prompt, model="gemini-2.0-flash-001", try_count=1)
        sys_prompt = random.choice(prompt_list)
        desc_dict = model_request(True, user_prompt, sys_prompt, model="gpt-4o-mini", try_count=1)
    if desc_dict.get("product_title", None) is not None and len(desc_dict.get("product_title")) >= 2:
        # model_name就取用5个长度标题
        desc_dict["model_name"] = " ".join(desc_dict.get("product_title").split(" ")[0:6])
    desc_dict["product_description"] = split_html_tag(desc_dict.get("product_description", ""))
    desc_dict["item_name"] = desc_dict.get("product_title", None)
    if not desc_dict.get("item_name"):
        desc_dict["item_name"] = None
    if not desc_dict.get("bullet_points"):
        desc_dict["bullet_points"] = None
    if not desc_dict.get("product_description", ""):
        desc_dict["product_description"] = None
    result_dict.update(desc_dict)


def is_number(s):
    return bool(re.match(r"^[+-]?(\d+(\.\d*)?|\.\d+)$", str(s)))


def select_option(_field_map, valid_values, field):
    optional_list = valid_values.get(field, [])
    if len(optional_list) == 0:
        return
    field_value = str(_field_map.get(field, "")).strip()  # 获取字段值
    for option in optional_list:
        if option.lower() == field_value.lower():
            _field_map[field] = option
            return
    # 如果字段值不在 optional_list 中，进行默认值填充
    if "LB" in optional_list:
        _field_map[field] = "LB"
    elif "IN" in optional_list:
        _field_map[field] = "IN"
    elif "Inches" in optional_list:
        _field_map[field] = "Inches"
    else:
        _field_map[field] = optional_list[len(optional_list) // 2]


def check_params(_field_map, _rule_map, need_check_list):
    keys_to_remove = [k for k, v in _field_map.items() if v is None or v == ""]
    for key in keys_to_remove:
        del _field_map[key]
    required_list = need_check_list
    required_list.append("item_type")
    _field_map["supplier_declared_dg_hz_regulation1"] = "Not Applicable"
    _field_map["supplier_declared_material_regulation1"] = "Not Applicable"
    valid_values = _rule_map["drop"]
    valid_values.update(_rule_map["valid_values"])
    # 对字段合法值和漏缺进行检查
    field_key_list = list(_field_map.keys())
    for required_field in required_list:
        optional_list = valid_values.get(required_field, [])
        if required_field not in field_key_list:
            if len(optional_list) == 0:
                def_data = _rule_map["data_definition"]
                if def_data.get(required_field, None) is None:
                    continue
                example = def_data[required_field].get("Example", None)
                if example is not None:
                    match = re.search(r'\d+', example)
                    if match:
                        _field_map[required_field] = 1
                    else:
                        _field_map[required_field] = example.split(",")[0] if "," in example else example
                else:
                    _field_map[required_field] = 1
            else:
                # 如果必填字段没有值但有参考值，使用 select_option 填充
                select_option(_field_map, valid_values, required_field)
        else:
            select_option(_field_map, valid_values, required_field)

    # 对单位的对称补充，只需要看单位字段即可，如果必填要求数值，那肯定单位也要求，即看一边即可
    unit_field_list = [x for x in _rule_map["template"] if x.endswith("_unit_of_measure")]
    for unit_field in unit_field_list:
        # 检查单位字段的合法值
        select_option(_field_map, valid_values, unit_field)
        # 获取对应的数值字段
        unit_value_field = unit_field.replace("_unit_of_measure", "")
        # 如果数值字段已填写但不是数字，则尝试提取数字
        if _field_map.get(unit_value_field, "") != "" and not is_number(_field_map.get(unit_value_field, "")):
            match = re.search(r'\d+', str(_field_map[unit_value_field]))
            if match:
                _field_map[unit_value_field] = int(match.group())
        # 如果单位字段已填写且数值字段未填写且该字段不在必填项中，删除单位字段
        if unit_field in _field_map and unit_value_field not in _field_map:
            if unit_field not in required_list:
                del _field_map[unit_field]
            else:
                _field_map[unit_value_field] = 1  # 如果单位是必填的，填充数值字段
        # 如果数值字段已填写且单位字段未填写，默认填充单位
        if unit_value_field in _field_map and unit_field not in _field_map:
            if unit_value_field not in required_list:
                del _field_map[unit_value_field]
                # 默认填充单位
            else:
                select_option(_field_map, valid_values, unit_field)


# 填充基础字段
def fill_base_field(_db_dict):
    search_words_map = json.loads(_db_dict.get('search_words', "{}"))
    if len(search_words_map) != 3:
        return {}
    keywords_next = search_words_map.get("Primary Keywords")[0:4] + search_words_map.get("Long-tail Keywords")[0:3] + search_words_map.get("Related Keywords")[0:3]
    generic_keywords = split_langer_txt(keywords_next)
    feed_product_type = _db_dict.get('product_type')
    packages = _db_dict.get('packages')
    dimensions = _db_dict.get('dimensions')
    images_list = _db_dict.get('images_list')
    images_list = images_list + (9 - len(images_list)) * ['']
    base_field_dict = _db_dict
    item_length = dimensions[0]
    item_width = dimensions[1]
    item_height = dimensions[2]
    base_field_dict.update({
        "generic_keywords": generic_keywords,
        "feed_product_type": feed_product_type,
        "item_type": _db_dict.get('catalog_name'),
        "main_image_url": images_list[0],
        "other_image_url1": images_list[1],
        "other_image_url2": images_list[2],
        "other_image_url3": images_list[3],
        "other_image_url4": images_list[4],
        "other_image_url5": images_list[5],
        "other_image_url6": images_list[6],
        "other_image_url7": images_list[7],
        "other_image_url8": images_list[8],
        "package_length": packages[0],
        "package_width": packages[1],
        "package_height": packages[2],
        "package_weight": packages[3],
        "item_length": item_length,
        "item_width": item_width,
        "item_height": item_height,
        "item_weight": dimensions[3],
        "item_display_length": item_length,
        "item_display_width": item_width,
        "item_display_height": item_height,
        "item_display_weight": dimensions[3]
    })
    # 追加新字段和单位
    base_field_dict["length_longer_edge"] = item_length
    base_field_dict["item_length_numeric"] = item_length
    base_field_dict["item_length_description"] = str(item_length) + "IN"
    base_field_dict["width_shorter_edge"] = item_width
    base_field_dict["item_dimension_width"] = item_width
    base_field_dict["product_width_dimension"] = item_width
    base_field_dict["item_thickness_dimension"] = item_width
    base_field_dict["seat_height"] = item_height
    base_field_dict["thickness_floor_to_top"] = item_height
    base_field_dict["product_height_dimension"] = item_height
    base_field_dict["item_height_dimension"] = item_height
    base_field_dict["maximum_height"] = item_height
    base_field_dict["thickness_head_to_toe"] = item_height
    base_field_dict["length_longer_edge_unit_of_measure"] = "Inches"
    base_field_dict["item_length_numeric_unit_of_measure"] = "Inches"
    base_field_dict["width_shorter_edge_unit_of_measure"] = "Inches"
    base_field_dict["product_width_dimension_unit_of_measure"] = "Inches"
    base_field_dict["item_thickness_dimension_unit_of_measure"] = "Inches"
    base_field_dict["seat_height_unit_of_measure"] = "Inches"
    base_field_dict["thickness_floor_to_top_unit_of_measure"] = "Inches"
    base_field_dict["product_height_dimension_unit_of_measure"] = "Inches"
    base_field_dict["item_height_dimension_unit_of_measure"] = "Inches"
    base_field_dict["maximum_height_unit_of_measure"] = "Inches"
    base_field_dict["item_dimension_width_unit_of_measure"] = "Inches"
    base_field_dict["thickness_head_to_toe_unit_of_measure"] = "Inches"

    # Default values/units for all products
    base_field_dict.update({
        "currency": "USD",
        "condition_type": "NEW",
        "country_of_origin": "China",
        "package_length_unit_of_measure": "IN",
        "package_height_unit_of_measure": "IN",
        "package_width_unit_of_measure": "IN",
        "package_weight_unit_of_measure": "LB",
        "item_length_unit_of_measure": "IN",
        "item_width_unit_of_measure": "IN",
        "item_height_unit_of_measure": "IN",
        "item_weight_unit_of_measure": "LB",
        "item_display_weight_unit_of_measure": "LB",
        "item_display_length_unit_of_measure": "IN",
        "item_display_width_unit_of_measure": "IN",
        "item_display_height_unit_of_measure": "IN",
        "is_expiration_dated_product": "No",
        "fulfillment_center_id": "DEFAULT",
        "included_components1": "others",
        "special_features1": "others"
    })

    # Dimensions with default values
    base_field_dict.update({
        "depth_front_to_back": item_length,
        "depth_front_to_back_unit_of_measure": "Inches",
        "depth_width_side_to_side": item_width,
        "depth_width_side_to_side_unit_of_measure": "Inches",
        "depth_height_floor_to_top": item_height,
        "depth_height_floor_to_top_unit_of_measure": "Inches",
        "height_width_side_to_side": item_width,
        "height_width_side_to_side_unit_of_measure": "Inches",
        "height_floor_top": item_height,
        "height_floor_top_unit_of_measure": "Inches",
        "length_head_to_toe": item_length,
        "length_width_side_to_side": item_width,
        "length_height_floor_to_top": item_height,
        "length_head_to_toe_unit_of_measure": "Inches",
        "length_width_side_to_side_unit_of_measure": "Inches",
        "length_height_floor_to_top_unit_of_measure": "Inches",
        "unit_count": 1,
        "unit_count_type": "Count"
    })
    return base_field_dict


# 去打标签
def thread_gen_amazon_field_json_and_update_db(basic_product_list, is_append=False, append_fields=None):
    if append_fields is None:
        append_fields = []
    create_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    need_add_list = []
    for tmp_product in basic_product_list:
        if not old_get_real_product_type(tmp_product.product_type):
            continue
        if "amazon" in tmp_product.not_available.lower():
            continue
        db_dict = json.loads(tmp_product.json_text)
        db_dict["id"] = tmp_product.id
        db_dict["platform"] = tmp_product.platform
        db_dict["product_type"] = tmp_product.product_type
        db_dict["class_name"] = tmp_product.class_name
        db_dict["catalog_name"] = tmp_product.catalog_name
        db_dict["product_info"] = tmp_product.product_info
        db_dict["search_words"] = tmp_product.search_words
        if db_dict.get('price'):
            del db_dict['price']
        if db_dict.get('quantity'):
            del db_dict['quantity']
        # Step 1: 收集手动启动的参数
        res_dict = fill_base_field(db_dict)
        if len(res_dict) == 0:
            return
        rule_map = old_get_xlsm_data_for_product(res_dict["feed_product_type"])
        if rule_map is None:
            return
        # 取出全量产品信息。替换掉采集时分割占位符
        prod_info = db_dict["product_info"]
        # Step 2: 准备好AI参数，以及提交AI
        required_field = list(set(rule_map["required"]) - set(rule_map["not_ai_field"]))
        required_field = [x for x in required_field if "battery" not in x or "batteries" not in x]
        required_field.extend([x.strip() for x in append_fields])
        extra_field = required_field
        definition_map = rule_map["data_definition"]
        valid_values = rule_map["drop"]
        valid_values.update(rule_map["valid_values"])
        if len(extra_field) > 0:
            if any("color" in item for item in extra_field):
                extra_field.append("color_map")
            extra_definition = {key: definition_map[key] for key in extra_field if key in definition_map}
            extra_valid_values = {key: valid_values[key] for key in extra_field if key in valid_values}
            gpt_guess_field_value_task(prod_info, extra_definition, extra_valid_values, res_dict)
        # Step 3,默认都不含有电池
        res_dict.update({"item_volume": "1", "item_volume_unit_of_measure": "Microliters", "are_batteries_included": "No", "batteries_required": "No"})
        # 进行参数校验
        check_params(res_dict, rule_map, required_field)
        # 更新amazon_us_tag
        temp_field = rule_map["required"]
        temp_field.extend(rule_map["not_ai_field"])
        base_field = list(set(temp_field) - set(extra_field))
        base_field.append("item_type")
        base_json = {key: res_dict[key] for key in base_field if key in res_dict}
        extra_json = {key: res_dict[key] for key in extra_field if key in res_dict}
        base_json_str = json.dumps(base_json) if base_json else "{}"
        extra_json_str = json.dumps(extra_json) if extra_json else "{}"
        amazon_us_tag_obj = db_get_one("amazon_us_tag", f"basic_id={res_dict['id']}", AmazonUsTag)
        if amazon_us_tag_obj is None and not is_append:
            values = [db_dict.get("id"), db_dict.get("platform"), db_dict.get("sku"), create_time, db_dict.get("product_type"), db_dict.get("class_name"), base_json_str, extra_json_str]
            need_add_list.append(values)
        elif amazon_us_tag_obj and is_append:
            update_fields = ["id", "base_json", "extra_json", "remark"]
            db_batch_update("amazon_us_tag", update_fields, [[amazon_us_tag_obj.id, base_json_str, extra_json_str, f'AI has re labeled origin:{",".join(append_fields)}']])
    if need_add_list:
        fields = ["basic_id", "basic_platform", "basic_sku", "create_time", "product_type", "class_name", "base_json", "extra_json"]
        db_batch_insert("amazon_us_tag", fields, need_add_list)


# 打标签
def batch_tag_amazon():
    (datetime.now().strftime("%Y-%m-%d %H:%M:%S"), 'start:AmazonUs.batch tag...')
    # 先获取已经打过标签的ID
    with ThreadPoolExecutor(max_workers=10) as executor:
        futures = []
        for page in range(1, 99999999):
            conditions = 'product_type !="" and search_words like "{%}" and id not in (select basic_id from amazon_us_tag) '
            basic_product_list = db_list_by_page("basic_product", ",".join(BasicProduct.fields), conditions, BasicProduct, page, 200)
            if not basic_product_list:
                break
            basic_product_list = [prod for prod in basic_product_list if "amazon" not in prod.not_available.lower()]
            if basic_product_list:
                sleep_random_duration()
                futures.append(executor.submit(thread_gen_amazon_field_json_and_update_db, basic_product_list))
        print(f"amazonUs tag wait thread...")
        for future in as_completed(futures):
            try:
                future.result()
            except Exception as e:
                print(f"amazonUs tag fail : {e}")
    print(datetime.now().strftime("%Y-%m-%d %H:%M:%S"), 'end:AmazonUs.tag...')


def thread_tag_amazon():
    print(datetime.now().strftime("%Y-%m-%d %H:%M:%S"), 'start:AmazonUs.tag...')


# 生成反差关键词
def batch_search_words():
    print(datetime.now().strftime("%Y-%m-%d %H:%M:%S"), 'start:product gen search words...')
    sensitive_map = old_get_common_data()["sensitive"]
    sensitive_words = [word for key, values in sensitive_map.items() if key not in ["need_filter_field"] for word in values]
    with ThreadPoolExecutor(max_workers=10) as executor:  # 每次并行处理10个线程
        futures = []
        for page in range(1, 9999999):
            conditions = 'product_type !="" and product_info is not null and (search_words NOT LIKE "{%" or search_words is null)'
            basic_prod_this = db_list_by_page("basic_product", "id,class_name,product_type,product_info,search_words", conditions, BasicProduct, page, 200)
            if not basic_prod_this:
                break
            futures.append(executor.submit(thread_search_words, basic_prod_this, sensitive_words))
            sleep_random_duration()
        print(f"search words, wait thread...")
        for future in as_completed(futures):
            try:
                future.result()
            except Exception as e:
                print(f"任务执行失败: {e}")
    print(datetime.now().strftime("%Y-%m-%d %H:%M:%S"), 'end:search words...')


def thread_search_words(basic_product_list, sensitive_words):
    need_update = []
    for basic_product in basic_product_list:
        product_info = remove_sensitive_words(basic_product.product_info, sensitive_words)
        res = model_request(True, f'product class:{basic_product.class_name}, product type: {basic_product.product_type}\nproduct information:\n{product_info}', search_words_sys_prompt, model="gpt-4o-mini", try_count=1)
        if not isinstance(res, dict) or len(res) != 3:
            # res = model_request(True, f'product class:{basic_product.class_name}, product type: {basic_product.product_type}\nproduct information:\n{product_info}', search_words_sys_prompt, model="gemini-2.0-flash-001", try_count=1)
            res = model_request(True, f'product class:{basic_product.class_name}, product type: {basic_product.product_type}\nproduct information:\n{product_info}', search_words_sys_prompt, model="gpt-4o-mini", try_count=1)
        if isinstance(res, dict) and len(res) == 3 and "Related Keywords" in res and "Long-tail Keywords" in res and "Primary Keywords" in res:
            need_update.append([basic_product.id, json.dumps(res)])
    db_batch_update("basic_product", ["id", "search_words"], need_update)


# 去组装产生xlsm数据入record库
def gen_amazon_to_xlsm(opt_name, brand, not_class_name_list, platform, brand_zip, shop, upload_id, upload_limit=1000, limit_img=2, price=30, process_core=12):
    logger.info("start:AmazonUs old gen xlsm...")
    platform_str = "1=1"
    not_class_str = "1=1"
    if platform:
        platform_str = f'bp.platform = "{platform}" '
    if not_class_name_list:
        not_class_name_list = [f'"{x}"' for x in not_class_name_list]
        not_class_str = f'bp.class_name not in ({",".join(not_class_name_list)}) '
    # 获取基础表中的所有该大类的，不需要判断是否可用于amazon平台，在打ai标签那已经判断
    not_update_type_list = '"WALL_ART","wallart"'
    condition = f"SELECT bp.id FROM basic_product bp INNER JOIN amazon_us_tag aut ON bp.id = aut.basic_id LEFT JOIN amazon_us_record aur ON bp.id = aur.basic_id AND aur.brand = '{brand}' WHERE {platform_str} and {not_class_str} and bp.product_type not in ({not_update_type_list}) and bp.quantity > 3 and bp.not_available not like '%amazon%' and bp.status = 1 AND bp.published = 1 AND bp.price >= {price}  AND bp.image_count >= {limit_img}  AND aur.basic_id IS NULL ORDER BY bp.product_type ASC"
    need_upload_id_list = []
    recur = 0
    while recur <= 10:
        need_upload_id_list = db_list_by_page_v2(condition, 1, upload_limit)
        if need_upload_id_list and len(need_upload_id_list) >= upload_limit // 10:
            break
        recur += 1
        condition = f"SELECT bp.id FROM basic_product bp INNER JOIN amazon_us_tag aut ON bp.id = aut.basic_id LEFT JOIN amazon_us_record{str(recur)} aur ON bp.id = aur.basic_id AND aur.brand = '{brand}' WHERE {platform_str} and {not_class_str} and bp.product_type not in ({not_update_type_list}) and bp.quantity > 3 and bp.not_available not like '%amazon%' and bp.status = 1 AND bp.published = 1 AND bp.price >= {price}  AND bp.image_count >= {limit_img}  AND aur.basic_id IS NULL ORDER BY bp.product_type ASC"
    if not need_upload_id_list:
        db_batch_update("amazon_us_upload", ["id", "status"], [[upload_id, 2]])
        send_dingtalk_message(f'Amazon-US上传表格重复铺货多次提前结束,\n品牌"{brand}"与条件:价格大于等于{price},至少图片:{limit_img}张已上完,请换个条件或品牌.')
        return
    need_upload_id_list = [x[0] for x in need_upload_id_list]
    # 从redis中获取最新的本地数据
    old_load_amazon_us_data_to_cache()
    # 各自商品的价格系数
    price_strategy_map = get_amazon_us_config_price_strategy()
    # 开始进行组装，以每页thread_page_size个进行查询
    threads = []
    logger.info(f'Amazon-US,old,{shop},gen count:{len(need_upload_id_list)}...')
    thread_page_size = len(need_upload_id_list) // process_core + 1
    for page in range(0, len(need_upload_id_list), thread_page_size):
        sleep_random_duration(0.5, 1)
        loop_need_id_str = [str(x) for x in need_upload_id_list[page:page + thread_page_size]]
        basic_product_list_page = db_list_by_page("basic_product", "id,platform,sku,class_name,product_type,search_words,price,ship_fee,quantity,product_info,json_text,extra_field", f'published = 1 and status = 1 and id in ({",".join(loop_need_id_str)})', BasicProduct, 1, thread_page_size * 2)
        tag_list_page = db_list_by_page("amazon_us_tag", "basic_id,base_json,extra_json", f'basic_id in ({",".join(loop_need_id_str)})', AmazonUsTag, 1, thread_page_size * 2)
        if tag_list_page is None:
            print(f"{datetime.now().strftime("%Y-%m-%d %H:%M:%S")},find a bug query tag....{page}")
            continue
        # 图片需要及时取用basic表的
        basic_img_map = {}
        for basic_prod in basic_product_list_page:
            basic_id = basic_prod.id
            json_text = json.loads(basic_prod.json_text)
            img_list = json_text.get("images_list", [])
            img_list = [x for x in img_list if x and len(x) > 5]
            # 对图片第三张后面进行随机排列
            images_list = shuffle_list_except_first_two_elements(img_list)
            images_list = images_list + (9 - len(images_list)) * ['']
            img_map = {"main_image_url": images_list[0], "other_image_url1": images_list[1], "other_image_url2": images_list[2], "other_image_url3": images_list[3], "other_image_url4": images_list[4], "other_image_url5": images_list[5], "other_image_url6": images_list[6],
                       "other_image_url7": images_list[7], "other_image_url8": images_list[8]}
            basic_img_map[basic_id] = img_map
        loop_tag_list = []
        for tag in tag_list_page:
            # 更替掉新的basic图片
            base_json = json.loads(tag.base_json)
            base_json.update(basic_img_map.get(tag.basic_id, {}))
            loop_tag_list.append([tag.basic_id, json.dumps(base_json), tag.extra_json])
        tag_map = {x[0]: [x[1], x[2]] for x in loop_tag_list}
        # 每个线程组的数据
        loop_dict = {}
        for basic_product in basic_product_list_page:
            basic_id = basic_product.id
            if not tag_map.get(basic_id, None):
                continue
            # 设置hand-time
            handling_time = ""
            has_ltl = "LTL" in (basic_product.extra_field or "")
            processing_time = json.loads(basic_product.extra_field).get("processing_time", 0) if basic_product.extra_field and "processing_time" in basic_product.extra_field else 0
            if basic_product.platform == "GG":
                handling_time = 5 if has_ltl else 3 if basic_product.sku.startswith("B") else 2
            elif basic_product.platform in ["SY", "XL"]:
                handling_time = 2
            elif basic_product.platform == "DB":
                if processing_time and int(processing_time) >= 2:
                    handling_time = processing_time
            tmp_dict = {"platform": basic_product.platform, "basic_sku": basic_product.sku, "product_type": basic_product.product_type,
                        "search_words": basic_product.search_words, "product_info": basic_product.product_info, "fulfillment_latency": handling_time}
            tmp_dict.update(json.loads(tag_map.get(basic_id)[0]))
            if len(tag_map.get(basic_id)[1]) > 0:
                try:
                    tmp_dict.update(json.loads(tag_map.get(basic_id)[1]))
                except Exception as e:
                    print(f"标签解析错误: {e},basic_id:{basic_id},tag:{tag_map.get(basic_id)[1]}")

            quantity = int(basic_product.quantity)
            # 需要加上运费,价格随机波动
            price = round(float(basic_product.price) + float(basic_product.ship_fee), 2)
            if not price:
                continue
            change_price = get_prod_price_by_class_name(price, basic_product.class_name, price_strategy_map)
            tmp_dict.update({"quantity": quantity, "price": change_price, "origin_price": price})
            loop_dict[basic_id] = tmp_dict
        # 启动线程，并将线程添加到线程列表中
        thread_ = threading.Thread(target=fill_content_field, args=(loop_dict, opt_name, brand, brand_zip, shop, recur))
        thread_.start()
        threads.append(thread_)
    # 去生产表格,等待所有线程执行完毕
    for this_thread in threads:
        this_thread.join()
    # # 所有线程执行完毕后，调用生成表格的函数
    logger.info("end:Amazon-US old gen table")
    gen_amazon_us_to_template_file(upload_id)
    print(f'定时任务已完成,任务ID:{upload_id}')



def fill_content_field(ai_dict_total, opt_name, brand, brand_zip, shop, recur):
    id_list = list(ai_dict_total.keys())
    datestr = datetime.now().strftime("%y%m%d")
    db_content_list = []
    db_record_list = []
    create_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    for index, _id in enumerate(id_list):
        _this_dict = ai_dict_total[_id]
        # 对反查流量词进行更替,每次选取随机
        search_words_map = json.loads(_this_dict.get('search_words', "{}"))
        if len(search_words_map) == 3:
            keywords_next = random.sample(search_words_map.get("Primary Keywords"), 4) + random.sample(search_words_map.get("Related Keywords"), 3) + random.sample(search_words_map.get("Long-tail Keywords"), 3)
            generic_keywords = split_langer_txt(keywords_next)
            _this_dict["generic_keywords"] = generic_keywords
        # 出文案,需要过滤产品信息品牌
        sensitive_map = old_get_common_data()["sensitive"]
        if not sensitive_map:
            continue
        sensitive_words = [word for key, values in sensitive_map.items() if key not in ["need_filter_field"] for word in values]
        filter_product_info = remove_sensitive_words(_this_dict.get("product_info"), sensitive_words)
        gpt_title_bullet_desc(filter_product_info, _this_dict)
        if any(key not in _this_dict or _this_dict[key] in (None, '', []) for key in ("item_name", "bullet_points", "product_description")):
            continue
        # 五个卖点语句
        len_bullet = _this_dict.get("bullet_points", [])
        if len_bullet is None or len(len_bullet) == 0:
            continue
        for i in range(len(len_bullet)):
            key = f"bullet_point{str(i + 1)}"
            value = _this_dict["bullet_points"][i]
            _this_dict[key] = value
        # 只进行敏感词过滤,和AI产生的牌子过滤
        sensitive_words = sensitive_map["words"] + sensitive_map.get("ai", [])
        for field_tmp in sensitive_map["need_filter_field"]:
            _this_dict[field_tmp] = remove_sensitive_words(_this_dict.get(field_tmp), sensitive_words)
        _this_dict["item_name"] = brand + " " + _this_dict.get("item_name")
        # 放入字段
        platform = _this_dict["platform"]
        basic_sku = _this_dict["basic_sku"]
        price = _this_dict["price"]
        origin_quantity = _this_dict["quantity"]
        _this_dict["quantity"] = 0 if origin_quantity < 4 else int(origin_quantity * 0.4)
        _this_dict["standard_price"] = price
        _this_dict["list_price"] = max(price, round(_this_dict["origin_price"] * 1.8, 2))
        # 额外加上
        rule_map = old_get_xlsm_data_for_product(_this_dict.get("product_type"))
        if rule_map and "required" in rule_map and "map_price" in rule_map["required"]:
            _this_dict["map_price"] = price
        item_sku = f'{platform}-{basic_sku}-{brand_zip}-{datestr}-{opt_name}'
        if len(item_sku) >= 40:
            item_sku = f'{platform}-{basic_sku}-{str(uuid.uuid4()).upper()[:2]}{opt_name}'
        # 临时加的
        _this_dict["supplier_declared_material_regulation1"] = "Not Applicable"
        _this_dict["is_expiration_dated_product"] = "No"
        _this_dict["item_sku"] = item_sku
        _this_dict["brand_name"] = brand
        _this_dict["manufacturer"] = brand
        random_str = str(uuid.uuid4()).upper().replace("-", "")[:10] + str(random.randint(0, 100))
        _this_dict["model"] = f'{random_str}{opt_name}'
        _this_dict["part_number"] = random_str
        # 加入纯数值过滤,对替换掉_unit_of_measure还有字段值的,和number_of,capacity
        for field, field_val in _this_dict.items():
            if rule_map and "required" in rule_map and field in rule_map["required"]:
                _this_dict[field] = extract_field_number(field, field_val)
        # 放入template表格的数据
        res_list = []
        if rule_map and "template" in rule_map:
            template_list = rule_map["template"] if rule_map["template"]  else []
            res_list = [_this_dict.get(key, "") for key in template_list]

        if res_list is None or len(res_list) == 0:
            print(f'货源商品添加失败,basic_id:{_id}')
            continue

        if any(isinstance(item, (dict, list)) for item in res_list):
            print(f"sku:{item_sku},find a not type,tmp skip")
        else:
            feed_product_type = _this_dict.get("feed_product_type")
            this_map = old_get_common_data().get("class_type_map")
            class_name = this_map.get(feed_product_type)
            content = {"item_name": _this_dict["item_name"], "bullet_points": _this_dict["bullet_points"], "product_description": _this_dict["product_description"], "model_name": _this_dict.get("model_name")}
            db_content_list.append([_id, basic_sku, platform, brand, json.dumps(content), create_time, class_name, feed_product_type])
            record = [0, create_time, item_sku, _id, platform, basic_sku, price, origin_quantity, opt_name, shop, brand,
                      brand_zip, json.dumps(res_list), class_name, feed_product_type, "wait admin upload"]
            db_record_list.append(record)

    if db_content_list:
        db_batch_insert("amazon_us_content", ["basic_id", "basic_sku", "basic_platform", "brand", "content", "create_time", "class_name", "product_type"], db_content_list)
    if db_record_list:
        # 这是重复铺货了
        if recur and recur > 0:
            db_batch_insert(f"amazon_us_record{str(recur)}", ["status", "create_time", "sku", "basic_id", "basic_platform", "basic_sku", "price", "quantity", "opt_name", "shop", "brand", "brand_zip", "template_field", "class_name", "product_type", "remark"], db_record_list)
            db_batch_insert("amazon_us_record", ["status", "create_time", "sku", "basic_id", "basic_platform", "basic_sku", "price", "quantity", "opt_name", "shop", "brand", "brand_zip", "template_field", "class_name", "product_type", "remark"], db_record_list)
        else:
            db_batch_insert("amazon_us_record", ["status", "create_time", "sku", "basic_id", "basic_platform", "basic_sku", "price", "quantity", "opt_name", "shop", "brand", "brand_zip", "template_field", "class_name", "product_type", "remark"], db_record_list)


def write_amazon_xlsm(_record_dict, datestr, shop, opt_name, upload_id):
    dest_folder = create_folder(shop, datestr + f"-product_{str(upload_id)}")
    product_type_list = list(set(_record_dict.keys()))
    for type_ in product_type_list:
        file_path = old_get_template_file_path(type_)
        new_file_path = copy_and_rename_file_with_row_check(file_path, dest_folder, f'{type_}-{opt_name}-{str(uuid.uuid4()).upper().replace("-", "")[:5]}-{str(upload_id)}.xlsm')
        write_to_amazon_template_xlsm(new_file_path, _record_dict[type_])




def gen_amazon_us_to_template_file(upload_id):
    logger.info(f"start:Amazon-US old gen table to file upload_id:{upload_id}")
    upload = db_get_one("amazon_us_upload", f"id={upload_id}", AmazonUsUpload)
    if upload is None:
        return
    opt_name = upload.opt_name
    shop = upload.shop
    conditions_ = f'status = 0 and opt_name="{opt_name}" and shop="{shop}"'
    record_list = db_list_by_page("amazon_us_record", "id,product_type,template_field", conditions_, AmazonUsRecord, 1, 9999999)
    if len(record_list) == 0:
        db_batch_update("amazon_us_upload", ["id", "status"], [[upload_id, 2]])
        return
    record_dict = {}
    need_update_record_db = []
    for record in record_list:
        id_ = record.id
        template_field = record.template_field
        product_type = record.product_type
        template_data = record_dict.get(product_type, [])
        template_data.append(json.loads(template_field))
        record_dict[product_type] = template_data
        need_update_record_db.append([id_, 1, "success xlsm"])
    datestr = datetime.now().strftime("%Y%m%d_%H%M%S")
    write_amazon_xlsm(record_dict, datestr, shop, opt_name, upload_id)
    db_batch_update("amazon_us_record", ["id", "status", "remark"], need_update_record_db)
    db_batch_update("amazon_us_upload", ["id", "status"], [[upload_id, 1]])
    logger.info(f"end:Amazon-US old gen table to file upload_id:{upload_id}")
    move_files_to_shared_folder()


def gen_delete_amazon_us_good_txt():
    logger.info("start:Amazon-US delete invalid sku to amazon")
    record_list = db_list_by_page("amazon_us_record", "sku,shop", f"basic_id in (select id from basic_product where published = 0 or status != 1 or not_available like '%amazon%')", AmazonUsRecord, 1, 999999)
    shop_skus_map = {}
    header = ["sku", "product-id", "product-id-type", "price", "minimum-seller-allowed-price", "maximum-seller-allowed-price", "item-condition", "quantity",
              "add-delete", "will-ship-internationally", "expedited-shipping", "standard-plus"    "item-note", "fulfillment-center-id", "product-tax-code", "handling-time",
              "business-price", "quantity-price-type", "quantity-lower-bound1", "quantity-price1", "quantity-lower-bound2", "quantity-price2", "quantity-lower-bound3", "quantity-price3", "quantity-lower-bound4", "quantity-price4", "quantity-lower-bound5", "quantity-price5",
              "progressive_discount_type", "progressive_discount_lower_bound1", "progressive_discount_value1",
              "progressive_discount_lower_bound2", "progressive_discount_value2", "progressive_discount_lower_bound3", "progressive_discount_value3", "pricing_action", "merchant_shipping_group_name", "batteries_required", "are_batteries_included", "battery_cell_composition",
              "battery_type", "number_of_batteries", "battery_weight", "battery_weight_unit_of_measure", "number_of_lithium_ion_cells", "number_of_lithium_metal_cells", "lithium_battery_packaging",
              "lithium_battery_energy_content", "lithium_battery_energy_content_unit_of_measure", "lithium_battery_weight", "lithium_battery_weight_unit_of_measure", "supplier_declared_dg_hz_regulation1", "supplier_declared_dg_hz_regulation2", "supplier_declared_dg_hz_regulation3",
              "supplier_declared_dg_hz_regulation4", "supplier_declared_dg_hz_regulation5", "hazmat_united_nations_regulatory_id", "safety_data_sheet_url", "item_weight", "item_weight_unit_of_measure",
              "item_volume", "item_volume_unit_of_measure", "flash_point", "ghs_classification_class1", "ghs_classification_class2", "ghs_classification_class3", "california_proposition_65_compliance_type", "california_proposition_65_chemical_names1",
              "california_proposition_65_chemical_names2",
              "california_proposition_65_chemical_names3", "california_proposition_65_chemical_names4", "california_proposition_65_chemical_names5"]
    sku_str_list = [f'"{x.sku}"' for x in record_list]
    for record in record_list:
        tmp_list = shop_skus_map.get(record.shop, [])
        val_list = len(header) * [""]
        val_list[0] = record.sku
        val_list[8] = 'x'
        tmp_list.append(val_list)
        shop_skus_map[record.shop] = tmp_list
    if len(sku_str_list) > 0:
        datestr = datetime.now().strftime("%y%m%d")
        # 按照店铺划分
        for shop in list(shop_skus_map.keys()):
            dest_folder = create_folder(shop, datestr + "-delete_sku")
            if len(shop_skus_map[shop]) > 0:
                gen_file_list = shop_skus_map[shop]
                for page in range(0, len(gen_file_list), 9999):
                    gen_this_file = gen_file_list[page: page + 9999]
                    gen_this_file.insert(0, header)
                    write_2d_list_to_txt(f'{dest_folder}{os.path.sep}{str(uuid.uuid4()).upper().replace("-", "")[:12]}-{shop}_{page}_del_sku.txt', gen_this_file)
    # 更新记录表的库存，basic的原库存
    if len(sku_str_list) > 0:
        db_delete("amazon_us_record", f'sku in ({",".join(sku_str_list)})')
    logger.info("end:Amazon-US delete invalid sku to amazon")
    move_files_to_shared_folder()

'''
# 更新亚马逊的库存,优化版本
def update_amazon_quantity_to_txt():
    sleep_random_duration(500, 600)
    task_record_change = {}
    need_update_record_db = []
    now = datetime.now()
    basic_list = []
    print("库存更新:库存更新进来了...")
    # 第一步:获取所有需要考虑的basic_product数据,这里只更新，状态正常且在售的数据，下架和非正常的 交给更新价格处。
    for page in range(1, 99999):
        basic_tmp_list = db_list_by_page("basic_product", "id,sku,quantity,published,status,extra_field,platform,not_available",
                                         f"id in (select DISTINCT(basic_id) from amazon_us_record) ", BasicProduct, page, 100000)
        if not basic_tmp_list:
            break
        basic_list.extend(basic_tmp_list)
    # 构建基础产品信息映射
    basic_product_map = {}
    for basic_prod in basic_list:
        basic_product_map[basic_prod.id] = {
            'basic_sku': basic_prod.sku,
            'platform': basic_prod.platform,
            'quantity': int(basic_prod.quantity) if "amazon" not in basic_prod.not_available and basic_prod.status == 1 and basic_prod.published == 1 else 0,
            'has_ltl': "LTL" in (basic_prod.extra_field or ""),
            'handling_time': json.loads(basic_prod.extra_field).get("processing_time", 0) if basic_prod.extra_field and "processing_time" in basic_prod.extra_field else 0
        }
    print("库存更新:货源信息查询完毕...")
    # TODO: 获取最近60分钟内有出单的SKU列表
    # recent_order_skus = get_recent_orders_skus(60)  # 未实现函数，需要接入亚马逊订单API
    recent_order_skus = set()  # 暂时假设没有出单
    # 按basic_id对记录进行分组
    grouped_records = {}
    # 第二步：按basic_id分组获取并处理记录
    for page in range(1, 999999):
        # 获取所有记录但按basic_id分组聚合
        records_to_process = db_list_by_page("amazon_us_record", "id,basic_id,sku,quantity,shop,update_time,first_zero_time", f"status=1", AmazonUsRecord, page, 200000)
        if not records_to_process:
            break
        for record in records_to_process:
            if record.basic_id not in grouped_records:
                grouped_records[record.basic_id] = []
            grouped_records[record.basic_id].append(record)
    print("库存更新:上架明细查询完毕...")
    # 对每个basic_id处理一次库存逻辑，然后应用到该basic_id的所有记录
    for basic_id, records in grouped_records.items():
        # 跳过无效的basic_id
        if basic_id not in basic_product_map:
            continue
        if not records:
            continue
        basic_info = basic_product_map[basic_id]
        basic_sku = basic_info['basic_sku']
        platform = basic_info['platform']
        db_quantity = basic_info['quantity']
        # 判断SKU类型（紧急/普通）
        is_urgent = False
        # 检查是否为紧急SKU
        if 4 <= db_quantity <= 20:  # 货源库存4-20
            is_urgent = True
        elif basic_sku in recent_order_skus:  # 最近60分钟内有出单
            is_urgent = True
        # 如果任意一个满足更新条件,则所有店铺都更新
        need_update = False
        last_update_time = now
        record_quantity = db_quantity
        first_zero_time = now

        # 遍历 records 列表
        for record in records:
            record_quantity = int(record.quantity if record.quantity else 0)
            last_update_time = record.update_time
            first_zero_time = record.first_zero_time
            if check_inventory_status_change(record_quantity, db_quantity):
                need_update = True
                break
        # 为该basic_id的所有记录应用相同的库存逻辑
        if need_update:
            is_urgent = True
        # 检查是否符合更新频率条件
        if db_quantity > 3:
            if is_urgent:
                # 紧急SKU：3小时更新一次
                if last_update_time and (now - last_update_time).total_seconds() < 9900:
                    continue
            else:
                # 普通SKU：6小时更新一次
                if last_update_time and (now - last_update_time).total_seconds() < 19800:
                    continue
        # 计算更新数量
        update_quantity = calculate_update_quantity(db_quantity, record_quantity)

        #计算首次推0时间:24小时内内重复推送，之后不推送了
        update_first_zero_time = calculate_update_quantity_zero(db_quantity, record_quantity,first_zero_time,now)

        #计算库存为0时候，是否需要去推送
        is_quantity_zero_update = False
        if (update_quantity == 0 or record_quantity == 0) and first_zero_time and (now - first_zero_time) <= timedelta(days=1):
            is_quantity_zero_update = True

        # 设置handling_time
        handling_time = ""
        if platform == "GG":
            handling_time = 5 if basic_info['has_ltl'] else 3 if basic_sku.startswith("B") else 2
        elif platform in ["SY", "XL"]:
            handling_time = 2
        elif platform == "DB":
            if basic_info['handling_time'] and int(basic_info['handling_time']) >= 2:
                handling_time = basic_info['handling_time']
        # 如果需要更新或者计算出的更新数量与当前不同,库存首次为0时，24小时内推送,超过24小时就不推送了
        if need_update or update_quantity != record_quantity or is_quantity_zero_update:
            # 只更新库存
            for record in records:
                shop = record.shop
                shop_record = task_record_change.get(shop, [])
                shop_record.append([record.sku, "", "", "", update_quantity, handling_time, ""])
                task_record_change[shop] = shop_record
                need_update_record_db.append([record.id, update_quantity, now,update_first_zero_time])
    # 去生成库存模板
    if task_record_change:
        datestr = datetime.now().strftime("%H_%M")
        date_folder = datetime.now().strftime("%Y-%m-%d")
        # 按照店铺划分
        for shop, records in task_record_change.items():
            if not records:
                continue
            dest_folder = create_folder(shop,date_folder)
            # 批量处理记录
            for page in range(0, len(records), 100000):
                gen_this_file = records[page: page + 100000]
                head_ = ["sku", "price", "minimum-seller-allowed-price", "maximum-seller-allowed-price", "quantity", "handling-time", "fulfillment-channel"]
                gen_this_file.insert(0, head_)
                file_name = f'{dest_folder}{os.path.sep}{datestr}_{str(uuid.uuid4()).upper()[:2]}{page}_quantity.txt'
                write_2d_list_to_txt(file_name, gen_this_file)
    move_files_to_shared_folder()
    # 批量更新记录表
    if need_update_record_db:
        # 使用批量更新提高性能
        for i in range(0, len(need_update_record_db), 100000):
            update_this_page = need_update_record_db[i: i + 100000]
            db_batch_update("amazon_us_record", ["id", "quantity", "update_time","first_zero_time"], update_this_page)
        logger.info(f"AmazonUs quantity Updated {len(need_update_record_db)} to txt  records.")
'''

def update_amazon_quantity_to_txt():
    sleep_random_duration(500, 600)
    task_record_change = {}
    need_update_record_db = []
    now = datetime.now()
    basic_list = []
    print("库存更新:库存更新进来了...")
    # 第一步:获取所有需要考虑的basic_product数据,这里只更新，状态正常且在售的数据，下架和非正常的 交给更新价格处。
    # for page in range(1, 99999):
    #     basic_tmp_list = db_list_by_page("basic_product", "id,sku,quantity,published,status,extra_field,platform,not_available",
    #                                      f"id in (select DISTINCT(basic_id) from amazon_us_record) ", BasicProduct, page, 100000)
    #     if not basic_tmp_list:
    #         break
    #     basic_list.extend(basic_tmp_list)
    last_id = 0
    page_size = 3000  # 调整到合理值
    while True:
        # 添加 WHERE 条件按 ID 排序分页
        condition = f"id > {last_id}"
        basic_tmp_list = db_list_by_page_v3("basic_product", "id,sku,quantity,published,status,extra_field,platform,not_available", condition, BasicProduct, 1, page_size)
        if not basic_tmp_list:
            break
        basic_list.extend(basic_tmp_list)
        last_id = basic_tmp_list[-1].id  # 记录最后一条记录的 ID

    print(f"库存更新:货源信息查询完毕...{len(basic_list)}")

    # 构建基础产品信息映射
    basic_product_map = {}
    for basic_prod in basic_list:
        basic_product_map[basic_prod.id] = {
            'basic_sku': basic_prod.sku,
            'platform': basic_prod.platform,
            'quantity': int(basic_prod.quantity) if "amazon" not in basic_prod.not_available and basic_prod.status == 1 and basic_prod.published == 1 else 0,
            'has_ltl': "LTL" in (basic_prod.extra_field or ""),
            'handling_time': json.loads(basic_prod.extra_field).get("processing_time", 0) if basic_prod.extra_field and "processing_time" in basic_prod.extra_field else 0
        }

    # TODO: 获取最近60分钟内有出单的SKU列表
    # recent_order_skus = get_recent_orders_skus(60)  # 未实现函数，需要接入亚马逊订单API
    recent_order_skus = set()  # 暂时假设没有出单
    # 按basic_id对记录进行分组
    grouped_records = {}
    # 第二步：按basic_id分组获取并处理记录

    # for page in range(1, 999999):
    #     # 获取所有记录但按basic_id分组聚合
    #     records_to_process = db_list_by_page("amazon_us_record", "id,basic_id,sku,quantity,shop,update_time,first_zero_time", f"status=1", AmazonUsRecord, page, 200000)
    #     if not records_to_process:
    #         break
    #     for record in records_to_process:
    #         if record.basic_id not in grouped_records:
    #             grouped_records[record.basic_id] = []
    #         grouped_records[record.basic_id].append(record)

    record_rows = 0

    last_id = 0
    page_size = 3000  # 调整到合理值
    while True:
        # 添加 WHERE 条件按 ID 排序分页
        condition = f"id > {last_id} and status=1"
        records_to_process = db_list_by_page_v3("amazon_us_record", "id,basic_id,sku,quantity,shop,update_time,first_zero_time", condition, AmazonUsRecord, 1, page_size)
        if not records_to_process:
            break
        record_rows = record_rows + len(records_to_process)
        last_id = records_to_process[-1].id  # 记录最后一条记录的 ID
        for record in records_to_process:
            if record.basic_id not in grouped_records:
                grouped_records[record.basic_id] = []
            grouped_records[record.basic_id].append(record)


    # 对每个basic_id处理一次价格逻辑，然后应用到该basic_id的所有记录
    print( f"库存更新:上架明细查询完毕...{record_rows}" )
   
    # 对每个basic_id处理一次库存逻辑，然后应用到该basic_id的所有记录
    for basic_id, records in grouped_records.items():
        # 跳过无效的basic_id
        if basic_id not in basic_product_map:
            continue
        if not records:
            continue
        basic_info = basic_product_map[basic_id]
        basic_sku = basic_info['basic_sku']
        platform = basic_info['platform']
        db_quantity = basic_info['quantity']
        # 判断SKU类型（紧急/普通）
        is_urgent = False
        # 检查是否为紧急SKU
        if 4 <= db_quantity <= 20:  # 货源库存4-20
            is_urgent = True
        elif basic_sku in recent_order_skus:  # 最近60分钟内有出单
            is_urgent = True
        # 如果任意一个满足更新条件,则所有店铺都更新
        need_update = False
        last_update_time = now
        record_quantity = db_quantity
        first_zero_time = now

        # 遍历 records 列表
        for record in records:
            record_quantity = int(record.quantity if record.quantity else 0)
            last_update_time = record.update_time
            first_zero_time = record.first_zero_time
            if check_inventory_status_change(record_quantity, db_quantity):
                need_update = True
                break
        # 为该basic_id的所有记录应用相同的库存逻辑
        if need_update:
            is_urgent = True
        # 检查是否符合更新频率条件
        if db_quantity > 3:
            if is_urgent:
                # 紧急SKU：3小时更新一次
                if last_update_time and (now - last_update_time).total_seconds() < 9900:
                    continue
            else:
                # 普通SKU：6小时更新一次
                if last_update_time and (now - last_update_time).total_seconds() < 19800:
                    continue
        # 计算更新数量
        update_quantity = calculate_update_quantity(db_quantity, record_quantity)

        #计算首次推0时间:24小时内内重复推送，之后不推送了
        update_first_zero_time = calculate_update_quantity_zero(db_quantity, record_quantity,first_zero_time,now)

        #计算库存为0时候，是否需要去推送
        is_quantity_zero_update = False
        if (update_quantity == 0 or record_quantity == 0) and first_zero_time and (now - first_zero_time) <= timedelta(days=1):
            is_quantity_zero_update = True

        # 设置handling_time
        handling_time = ""
        if platform == "GG":
            handling_time = 5 if basic_info['has_ltl'] else 3 if basic_sku.startswith("B") else 2
        elif platform in ["SY", "XL"]:
            handling_time = 2
        elif platform == "DB":
            if basic_info['handling_time'] and int(basic_info['handling_time']) >= 2:
                handling_time = basic_info['handling_time']
        # 如果需要更新或者计算出的更新数量与当前不同,库存首次为0时，24小时内推送,超过24小时就不推送了
        if need_update or update_quantity != record_quantity or is_quantity_zero_update:
            # 只更新库存
            for record in records:
                shop = record.shop
                shop_record = task_record_change.get(shop, [])
                shop_record.append([record.sku, "", "", "", update_quantity, handling_time, ""])
                task_record_change[shop] = shop_record
                need_update_record_db.append([record.id, update_quantity, now,update_first_zero_time])
    # 去生成库存模板
    if task_record_change:
        datestr = datetime.now().strftime("%H_%M")
        date_folder = datetime.now().strftime("%Y-%m-%d")
        # 按照店铺划分
        for shop, records in task_record_change.items():
            if not records:
                continue
            dest_folder = create_folder(shop,date_folder)
            # 批量处理记录
            for page in range(0, len(records), 100000):
                gen_this_file = records[page: page + 100000]
                head_ = ["sku", "price", "minimum-seller-allowed-price", "maximum-seller-allowed-price", "quantity", "handling-time", "fulfillment-channel"]
                gen_this_file.insert(0, head_)
                file_name = f'{dest_folder}{os.path.sep}{datestr}_{str(uuid.uuid4()).upper()[:2]}{page}_quantity.txt'
                write_2d_list_to_txt(file_name, gen_this_file)
    move_files_to_shared_folder()
    # 批量更新记录表
    if need_update_record_db:
        # 使用批量更新提高性能
        for i in range(0, len(need_update_record_db), 100000):
            update_this_page = need_update_record_db[i: i + 100000]
            db_batch_update("amazon_us_record", ["id", "quantity", "update_time","first_zero_time"], update_this_page)
        logger.info(f"AmazonUs quantity Updated {len(need_update_record_db)} to txt  records.")


# 判断库存状态是否发生变化
def check_inventory_status_change(record_quantity, db_quantity):
    """
    检查库存状态是否发生变化：
    - 从安全值降至低库存时(>50降至<=50)
    - 从无库存变成有库存(<=3变为>3)
    """
    if (record_quantity > 50 and db_quantity <= 50) or (record_quantity <= 3 and db_quantity > 3):
        return True
    return False


# 计算更新的库存数量
def calculate_update_quantity(db_quantity, record_quantity):
    """
    根据货源库存计算亚马逊平台应设置的库存数量
    """
    # 货源库存 ≤ 3：亚马逊库存设为0
    if db_quantity <= 3:
        # 仅当当前记录库存不为0时更新，避免重复更新
        if record_quantity != 0: #首次推0
            return 0
        return record_quantity

    # 货源库存 4-50：亚马逊库存 = 货源库存 * 50%
    elif 4 <= db_quantity <= 50:
        new_quantity = int(db_quantity * 0.5)
        # 只有当库存变化大于40%时更新
        if abs(new_quantity - record_quantity) > 0.4 * record_quantity or record_quantity == 0:
            return new_quantity
        return record_quantity

    # 货源库存 > 50：暂不更新
    else:
        # TODO: 检查是否最近30分钟内有出单
        # has_recent_order = check_recent_orders(basic_sku, 30)  # 未实现函数
        has_recent_order = False  # 暂时假设没有出单

        # 首次涨至>50时更新
        if record_quantity < 25 or has_recent_order:
            return int(db_quantity * 0.5)
        return record_quantity

# 计算库存为0是否要推送
def calculate_update_quantity_zero(db_quantity, record_quantity,first_zero_time,now):
    """
    根据货源库存计算亚马逊平台应设置的库存数量
    """
    # 货源库存 ≤ 3：亚马逊库存设为0
    if db_quantity <= 3:
        # 首次推0:当前记录库存不为0时更新首次推0时间; 不是首次推0则不更新首次推0时间
        if record_quantity != 0:  # 首次推0
            return now
        return first_zero_time

    # 货源库存>3: 有库存了，首次推0时间设置为None
    else:
        return None



# 更新亚马逊的价格,优化版本
'''
def update_amazon_price_to_txt():
    print("价格更新进来了...")
    task_record_change = {}
    need_update_record_db = []
    need_update_record_quantity_db = []
    price_strategy_map = get_amazon_us_config_price_strategy()
    basic_list = []
    # 第一步：获取所有需要考虑的basic_product数据
    for page in range(1, 99999):
        basic_tmp_list = db_list_by_page("basic_product", "id,price,ship_fee,published,status,class_name,extra_field,not_available",
                                         " id in (select DISTINCT(basic_id) from amazon_us_record)", BasicProduct, page, 100000)
        if not basic_tmp_list:
            break
        basic_list.extend(basic_tmp_list)

    # 构建基础产品信息映射
    basic_product_map = {}
    for basic_prod in basic_list:
        is_valid = basic_prod.published == 1 and basic_prod.status == 1 and "amazon" not in basic_prod.not_available
        basic_price = round(float(basic_prod.price) + float(basic_prod.ship_fee), 2) if is_valid else 0
        basic_product_map[basic_prod.id] = {'price': basic_price, 'class_name': basic_prod.class_name, 'is_valid': is_valid}
    # 按basic_id对记录进行分组
    print("价格查询记录表...")
    grouped_records = {}
    # 第二步：按basic_id分组获取并处理记录
    for page in range(1, 9999999):
        # 获取所有记录但按basic_id分组聚合
        records_to_process = db_list_by_page("amazon_us_record", "id,basic_id,sku,price,quantity,shop,first_compare_time",
                                             f"status=1", AmazonUsRecord, page, 200000)
        if not records_to_process:
            break
        for record in records_to_process:
            if record.basic_id not in grouped_records:
                grouped_records[record.basic_id] = []
            grouped_records[record.basic_id].append(record)
    # 对每个basic_id处理一次价格逻辑，然后应用到该basic_id的所有记录
    print("价格查询记录表完毕...")
    current_time = datetime.now()
    for basic_id, records in grouped_records.items():
        # 跳过无效的basic_id
        if basic_id not in basic_product_map:
            continue
        basic_info = basic_product_map[basic_id]
        db_price = basic_info['price']
        class_name = basic_info['class_name']
        is_valid = basic_info['is_valid']

        first_compare_time = record.first_compare_time if record.first_compare_time else None

        update_first_compare_time = first_compare_time

        for record in records:
            record_price = round(float(record.price if record.price else 0), 2)
            shop = record.shop

            if is_valid:
                # 价格计算策略
                change_price = get_prod_price_by_class_name(db_price, class_name, price_strategy_map)
                # （相差0.6 且 15%以内）或者（相差15%以上且7天以上）
                need_change_flag = False

                if (change_price - record_price > 0) or (abs(change_price - record_price) > 0.6 and abs((change_price - record_price) / record_price) <= 0.15):
                    need_change_flag = True
                else:

                    if abs((change_price - record_price) / record_price) > 0.15:
                        if first_compare_time:
                            if (current_time - first_compare_time) > timedelta(days=7):
                                need_change_flag = True
                                update_first_compare_time = None
                        else:
                            update_first_compare_time = current_time #更新时间,不更新价格
                            change_price = record_price

                # 仅当价格变化超过0.51时更新
                if need_change_flag:
                    shop_record = task_record_change.get(shop, [])
                    # 添加价格更新记录，只更新价格，库存字段留空
                    shop_record.append([record.sku, change_price, "", "", "", "", ""])
                    task_record_change[shop] = shop_record

                if need_change_flag or update_first_compare_time != first_compare_time:
                    need_update_record_db.append([record.id, change_price, update_first_compare_time])


            else:
                # 对于无效的商品（status != 1 或 published != 1），设置库存为0 原始记录是0了则无需再产生文件。
                if int(record.quantity if record.quantity else 0) != 0:
                    shop_record = task_record_change.get(shop, [])
                    shop_record.append([record.sku, "", "", "", 0, "", ""])
                    task_record_change[shop] = shop_record
                    need_update_record_db.append([record.id, record_price,update_first_compare_time])
                    need_update_record_quantity_db.append([record.id, 0])
    # 去生成价格模板
    if task_record_change:
        datestr = datetime.now().strftime("%H_%M")
        date_folder = datetime.now().strftime("%Y-%m-%d")
        # 按照店铺划分
        for shop, records in task_record_change.items():
            if not records:
                continue
            dest_folder = create_folder(shop,date_folder)
            # 批量处理记录
            for page in range(0, len(records), 100000):
                gen_this_file = records[page: page + 100000]
                head_ = ["sku", "price", "minimum-seller-allowed-price", "maximum-seller-allowed-price", "quantity", "handling-time", "fulfillment-channel"]
                gen_this_file.insert(0, head_)
                file_name = f'{dest_folder}{os.path.sep}{datestr}_{str(uuid.uuid4()).upper()[:2]}{page}_price.txt'
                write_2d_list_to_txt(file_name, gen_this_file)
    move_files_to_shared_folder()
    # 批量更新记录表
    if need_update_record_db:
        # 使用批量更新提高性能
        for i in range(0, len(need_update_record_db), 100000):
            update_this_page = need_update_record_db[i: i + 100000]
            db_batch_update("amazon_us_record", ["id", "price","first_compare_time"], update_this_page)
    # 主动下架的,和非库存原因下架的
    if need_update_record_quantity_db:
        db_batch_update("amazon_us_record", ["id", "quantity"], need_update_record_quantity_db)
    logger.info(f"AmazonUs price Updated {len(need_update_record_db)} to txt records.")
'''

def update_amazon_price_to_txt():
    print("价格更新进来了...")
    task_record_change = {}
    need_update_record_db = []
    need_update_record_quantity_db = []
    price_strategy_map = get_amazon_us_config_price_strategy()
    basic_list = []
    # 第一步：获取所有需要考虑的basic_product数据
    last_id = 0
    page_size = 3000  # 调整到合理值
    while True:
        # 添加 WHERE 条件按 ID 排序分页
        condition = f"id > {last_id}"
        basic_tmp_list = db_list_by_page_v3("basic_product", "id,price,ship_fee,published,status,class_name,extra_field,not_available", condition, BasicProduct, 1, page_size)
        if not basic_tmp_list:
            break
        basic_list.extend(basic_tmp_list)
        last_id = basic_tmp_list[-1].id  # 记录最后一条记录的 ID

    print(f"价格更新：查询货源表完毕...{len(basic_list)}")
    # 构建基础产品信息映射
    basic_product_map = {}
    for basic_prod in basic_list:
        is_valid = basic_prod.published == 1 and basic_prod.status == 1 and "amazon" not in basic_prod.not_available
        basic_price = round(float(basic_prod.price) + float(basic_prod.ship_fee), 2) if is_valid else 0
        basic_product_map[basic_prod.id] = {'price': basic_price, 'class_name': basic_prod.class_name, 'is_valid': is_valid}

    # 按basic_id对记录进行分组

    grouped_records = {}
    # 第二步：按basic_id分组获取并处理记录
    # for page in range(1, 9999999):
    #     # 获取所有记录但按basic_id分组聚合
    #     records_to_process = db_list_by_page("amazon_us_record", "id,basic_id,sku,price,quantity,shop,first_compare_time",
    #                                          f"status=1", AmazonUsRecord, page, 200000)
    #     if not records_to_process:
    #         break
    #     for record in records_to_process:
    #         if record.basic_id not in grouped_records:
    #             grouped_records[record.basic_id] = []
    #         grouped_records[record.basic_id].append(record)

    record_rows = 0

    last_id = 0
    page_size = 3000  # 调整到合理值
    while True:
        # 添加 WHERE 条件按 ID 排序分页
        condition = f"id > {last_id} and status=1"
        records_to_process = db_list_by_page_v3("amazon_us_record", "id,basic_id,sku,price,quantity,shop,first_compare_time", condition, AmazonUsRecord, 1, page_size)
        if not records_to_process:
            break
        record_rows = record_rows + len(records_to_process)

        last_id = records_to_process[-1].id  # 记录最后一条记录的 ID
        for record in records_to_process:
            if record.basic_id not in grouped_records:
                grouped_records[record.basic_id] = []
            grouped_records[record.basic_id].append(record)


    # 对每个basic_id处理一次价格逻辑，然后应用到该basic_id的所有记录
    print( f"价格更新：查询上架明细记录表完毕...{record_rows}" )

    current_time = datetime.now()
    for basic_id, records in grouped_records.items():
        # 跳过无效的basic_id
        if basic_id not in basic_product_map:
            continue
        basic_info = basic_product_map[basic_id]
        db_price = basic_info['price']
        class_name = basic_info['class_name']
        is_valid = basic_info['is_valid']

        first_compare_time = record.first_compare_time if record.first_compare_time else None

        update_first_compare_time = first_compare_time

        for record in records:
            record_price = round(float(record.price if record.price else 0), 2)
            shop = record.shop

            if is_valid:
                # 价格计算策略
                change_price = get_prod_price_by_class_name(db_price, class_name, price_strategy_map)
                # （相差0.6 且 15%以内）或者（相差15%以上且7天以上）
                need_change_flag = False

                if (change_price - record_price > 0) or (abs(change_price - record_price) > 0.6 and abs((change_price - record_price) / record_price) <= 0.15):
                    need_change_flag = True
                else:

                    if abs((change_price - record_price) / record_price) > 0.15:
                        if first_compare_time:
                            if (current_time - first_compare_time) > timedelta(days=7):
                                need_change_flag = True
                                update_first_compare_time = None
                        else:
                            update_first_compare_time = current_time #更新时间,不更新价格
                            change_price = record_price

                # 仅当价格变化超过0.51时更新
                if need_change_flag:
                    shop_record = task_record_change.get(shop, [])
                    # 添加价格更新记录，只更新价格，库存字段留空
                    shop_record.append([record.sku, change_price, "", "", "", "", ""])
                    task_record_change[shop] = shop_record

                if need_change_flag or update_first_compare_time != first_compare_time:
                    need_update_record_db.append([record.id, change_price, update_first_compare_time])


            else:
                # 对于无效的商品（status != 1 或 published != 1），设置库存为0 原始记录是0了则无需再产生文件。
                if int(record.quantity if record.quantity else 0) != 0:
                    shop_record = task_record_change.get(shop, [])
                    shop_record.append([record.sku, "", "", "", 0, "", ""])
                    task_record_change[shop] = shop_record
                    need_update_record_db.append([record.id, record_price,update_first_compare_time])
                    need_update_record_quantity_db.append([record.id, 0])
    # 去生成价格模板
    if task_record_change:
        datestr = datetime.now().strftime("%H_%M")
        date_folder = datetime.now().strftime("%Y-%m-%d")
        # 按照店铺划分
        for shop, records in task_record_change.items():
            if not records:
                continue
            dest_folder = create_folder(shop,date_folder)
            # 批量处理记录
            for page in range(0, len(records), 100000):
                gen_this_file = records[page: page + 100000]
                head_ = ["sku", "price", "minimum-seller-allowed-price", "maximum-seller-allowed-price", "quantity", "handling-time", "fulfillment-channel"]
                gen_this_file.insert(0, head_)
                file_name = f'{dest_folder}{os.path.sep}{datestr}_{str(uuid.uuid4()).upper()[:2]}{page}_price.txt'
                write_2d_list_to_txt(file_name, gen_this_file)
    move_files_to_shared_folder()
    # 批量更新记录表
    if need_update_record_db:
        # 使用批量更新提高性能
        for i in range(0, len(need_update_record_db), 100000):
            update_this_page = need_update_record_db[i: i + 100000]
            db_batch_update("amazon_us_record", ["id", "price","first_compare_time"], update_this_page)
    # 主动下架的,和非库存原因下架的
    if need_update_record_quantity_db:
        db_batch_update("amazon_us_record", ["id", "quantity"], need_update_record_quantity_db)
    logger.info(f"AmazonUs price Updated {len(need_update_record_db)} to txt records.")




# 更新亚马逊的价格和库存
def update_amazon_price_quantity_to_txt():

    '''
    sleep_random_duration(10, 20)
    task_record_change = {}
    need_update_product_amazon_relate_map = {}
    basic_list = []
    basic_product_amazon_relate_list = []
    price_strategy_map = get_amazon_us_config_price_strategy()
    current_time = datetime.now()


    for page in range(1, 99999):
        basic_tmp_list = db_list_by_page_v3("basic_product", "id,price,ship_fee,published,status,class_name,extra_field,not_available", "",BasicProduct,page,100000)
        if not basic_tmp_list:
            break
        basic_list.extend(basic_tmp_list)

    # 构建基础产品信息映射
    basic_product_map = {}
    for basic_prod in basic_list:
        basic_product_map[basic_prod.id] = {
            'basic_sku': basic_prod.sku,
            'platform': basic_prod.platform,
            'quantity': int(basic_prod.quantity) if "amazon" not in basic_prod.not_available and basic_prod.status == 1 and basic_prod.published == 1 else 0,
            'has_ltl': "LTL" in (basic_prod.extra_field or ""),
            'handling_time': json.loads(basic_prod.extra_field).get("processing_time", 0) if basic_prod.extra_field and "processing_time" in basic_prod.extra_field else 0,

        }
        is_valid = basic_prod.published == 1 and basic_prod.status == 1 and "amazon" not in basic_prod.not_available
        basic_price = round(float(basic_prod.price) + float(basic_prod.ship_fee), 2) if is_valid else 0
        basic_product_map[basic_prod.id] = {'price': basic_price, 'class_name': basic_prod.class_name, 'is_valid': is_valid, "db_update_time": basic_prod.update_time if basic_prod.update_time else datetime.now()}

    # 查询价格库存映射表
    for page in range(1, 99999):
        basic_map_tmp_list = db_list_by_page_v3("basic_product_map","basic_id,sku,basic_price,basic_quantity,status,amazon_price,amazon_quantity","",BasicProductMap, page, 100000)

        if not basic_map_tmp_list:
            break
        basic_product_amazon_relate_list.extend(basic_map_tmp_list)

    for basic_product_amazon_relate in basic_product_amazon_relate_list:

        if basic_product_amazon_relate is None:
            continue
        # 跳过无效的basic_id
        basic_id = basic_product_amazon_relate.basic_id
        if basic_id not in basic_product_map:
            continue
        # 货源信息
        basic_info = basic_product_map[basic_id]

        # 价格更新
        db_price = basic_info['price']
        class_name = basic_info['class_name']
        is_valid = basic_info['is_valid']
        db_update_time = basic_info['db_update_time']
        record_price = round(float(basic_product_amazon_relate.amazon_price if basic_product_amazon_relate.amazon_price else 0), 2)

        if is_valid:
            # 价格计算策略
            change_price = get_prod_price_by_class_name(db_price, class_name, price_strategy_map)
            # （相差0.6 且 15%以内）或者（相差15%以上且7天以上）

            need_change_flag = (change_price - record_price > 0) or (abs(change_price - record_price) > 0.6 and abs((change_price - record_price) / record_price) <= 0.15) or (
                    abs((change_price - record_price) / record_price) > 0.15 and (current_time - db_update_time) > timedelta(days=7))
            # 仅当价格变化超过0.51时更新
            if need_change_flag:
                need_update_product_amazon_relate_map[basic_id] = ["",change_price] #存储需要更新的数据

        else:
            # 对于无效的商品（status != 1 或 published != 1），设置库存为0 原始记录是0了则无需再产生文件。
            if int(basic_product_amazon_relate.quantity if basic_product_amazon_relate.quantity else 0) != 0:
                need_update_product_amazon_relate_map[basic_id] = [0, record_price]  # 存储需要更新的数据

        # 库存更新
        basic_sku = basic_info['basic_sku']
        platform = basic_info['platform']
        db_quantity = basic_info['quantity']
        recent_order_skus = set()  # 暂时假设没有出单
        # 判断SKU类型（紧急/普通）
        is_urgent = False
        # 检查是否为紧急SKU
        if 4 <= db_quantity <= 20:  # 货源库存4-20
            is_urgent = True
        elif basic_sku in recent_order_skus:  # 最近60分钟内有出单
            is_urgent = True

        need_update = False
        last_update_time = current_time
        first_zero_time = current_time


        record_quantity = int(basic_product_amazon_relate.quantity if basic_product_amazon_relate.quantity else 0)
        last_update_time = basic_product_amazon_relate.update_time
        first_zero_time = basic_product_amazon_relate.first_zero_time

        if check_inventory_status_change(record_quantity, db_quantity):
            need_update = True
            break
'''

'''
#################################################
    #先处理价格
    # update_amazon_price()
    # 后处理库存
    # update_amazon_quantity()
    #制表


    # TODO: 获取最近60分钟内有出单的SKU列表
    # recent_order_skus = get_recent_orders_skus(60)  # 未实现函数，需要接入亚马逊订单API
    recent_order_skus = set()  # 暂时假设没有出单
    # 按basic_id对记录进行分组
    grouped_records = {}
    # 第二步：按basic_id分组获取并处理记录
    # for page in range(1, 999999):
    #     # 获取所有记录但按basic_id分组聚合
    #     records_to_process = db_list_by_page("amazon_us_record", "id,basic_id,sku,quantity,shop,update_time,first_zero_time", f"status=1", AmazonUsRecord, page, 200000)
    #     if not records_to_process:
    #         break
    #     for record in records_to_process:
    #         if record.basic_id not in grouped_records:
    #             grouped_records[record.basic_id] = []
    #         grouped_records[record.basic_id].append(record)
    # 对每个basic_id处理一次库存逻辑，然后应用到该basic_id的所有记录
    for basic_id, records in grouped_records.items():
        # # 跳过无效的basic_id
        # if basic_id not in basic_product_map:
        #     continue
        # if not records:
        #     continue
        #
        # basic_info = basic_product_map[basic_id]
        # basic_sku = basic_info['basic_sku']
        # platform = basic_info['platform']
        # db_quantity = basic_info['quantity']
        # 判断SKU类型（紧急/普通）
        is_urgent = False
        # 检查是否为紧急SKU
        if 4 <= db_quantity <= 20:  # 货源库存4-20
            is_urgent = True
        elif basic_sku in recent_order_skus:  # 最近60分钟内有出单
            is_urgent = True
        # 如果任意一个满足更新条件,则所有店铺都更新
        need_update = False
        last_update_time = now
        record_quantity = db_quantity
        first_zero_time = now

        # 遍历 records 列表
        for record in records:
            record_quantity = int(record.quantity if record.quantity else 0)
            last_update_time = record.update_time
            first_zero_time = record.first_zero_time
            if check_inventory_status_change(record_quantity, db_quantity):
                need_update = True
                break
        # 为该basic_id的所有记录应用相同的库存逻辑
        if need_update:
            is_urgent = True
        # 检查是否符合更新频率条件
        if db_quantity > 3:
            if is_urgent:
                # 紧急SKU：3小时更新一次
                if last_update_time and (now - last_update_time).total_seconds() < 9900:
                    continue
            else:
                # 普通SKU：6小时更新一次
                if last_update_time and (now - last_update_time).total_seconds() < 19800:
                    continue
        # 计算更新数量
        update_quantity = calculate_update_quantity(db_quantity, record_quantity)

        #计算首次推0时间:24小时内内重复推送，之后不推送了
        update_first_zero_time = calculate_update_quantity_zero(db_quantity, record_quantity,first_zero_time,now)

        #计算库存为0时候，是否需要去推送
        is_quantity_zero_update = False
        if (update_quantity == 0 or record_quantity == 0) and first_zero_time and (now - first_zero_time) <= timedelta(days=1):
            is_quantity_zero_update = True

        # 设置handling_time
        handling_time = ""
        if platform == "GG":
            handling_time = 5 if basic_info['has_ltl'] else 3 if basic_sku.startswith("B") else 2
        elif platform in ["SY", "XL"]:
            handling_time = 2
        elif platform == "DB":
            if basic_info['handling_time'] and int(basic_info['handling_time']) >= 2:
                handling_time = basic_info['handling_time']
        # 如果需要更新或者计算出的更新数量与当前不同,库存首次为0时，24小时内推送,超过24小时就不推送了
        if need_update or update_quantity != record_quantity or is_quantity_zero_update:
            # 只更新库存
            for record in records:
                shop = record.shop
                shop_record = task_record_change.get(shop, [])
                shop_record.append([record.sku, "", "", "", update_quantity, handling_time, ""])
                task_record_change[shop] = shop_record
                need_update_record_db.append([record.id, update_quantity, now,update_first_zero_time])
    # 去生成库存模板
    if task_record_change:
        datestr = datetime.now().strftime("%H_%M")
        date_folder = datetime.now().strftime("%Y-%m-%d")
        # 按照店铺划分
        for shop, records in task_record_change.items():
            if not records:
                continue
            dest_folder = create_folder(shop,date_folder)
            # 批量处理记录
            for page in range(0, len(records), 100000):
                gen_this_file = records[page: page + 100000]
                head_ = ["sku", "price", "minimum-seller-allowed-price", "maximum-seller-allowed-price", "quantity", "handling-time", "fulfillment-channel"]
                gen_this_file.insert(0, head_)
                file_name = f'{dest_folder}{os.path.sep}{datestr}_{str(uuid.uuid4()).upper()[:2]}{page}_quantity.txt'
                write_2d_list_to_txt(file_name, gen_this_file)
    move_files_to_shared_folder()
    # 批量更新记录表
    if need_update_record_db:
        # 使用批量更新提高性能
        for i in range(0, len(need_update_record_db), 100000):
            update_this_page = need_update_record_db[i: i + 100000]
            db_batch_update("amazon_us_record", ["id", "quantity", "update_time","first_zero_time"], update_this_page)
        logger.info(f"AmazonUs quantity Updated {len(need_update_record_db)} to txt  records.")
'''


if __name__ == '__main__':
    # update_amazon_quantity_to_txt()
    # update_amazon_price_to_txt()
    batch_search_words()
    batch_tag_amazon()
    pass
